<?php
//SQL 语句：select location.* from (select *,round(6378.138*2*asin(sqrt(pow(sin( (36.668530*pi()/180-px_lat*pi()/180)/2),2)+cos(36.668530*pi()/180)*cos(px_lat*pi()/180)* pow(sin( (117.020359*pi()/180-px_lon*pi()/180)/2),2)))*1000)  as distance from bsx_training where (px_state = 1) and (type_id != '') and (((px_lat >= 27.683290277922) and (px_lat <= 45.653769722078)) and ((px_lon >= 105.81826766053) and (px_lon <= 128.22245033947))) order by  distance limit 0,10) location  where (1=1) and (location.distance <= 500)
// lon1当前用户经度 lat1当前用户纬度，lon2 sql的经度字段 lat sql的纬度字段
function distance_sql($lon1,$lat1,$lon2,$lat2)
{
    $sql = "round(6378.138*2*asin(sqrt(pow(sin( ({$lat1}*pi()/180-{$lat2}*pi()/180)/2),2)+cos({$lat1}*pi()/180)*cos({$lat2}*pi()/180)* pow(sin( ({$lon1}*pi()/180-{$lon2}*pi()/180)/2),2)))*1000) ";
    return $sql;
}


/**
 *
 * @param  $latitude    纬度
 * @param  $longitude    经度
 * @param  $raidus        半径范围(单位：米)
 * @return multitype:number
 */
function getAround($latitude,$longitude,$raidus)
{
    $PI = 3.14159265;
    $degree = (24901*1609)/360.0;
    $dpmLat = 1/$degree;
    $radiusLat = $dpmLat*$raidus;
    $minLat = $latitude - $radiusLat;
    $maxLat = $latitude + $radiusLat;
    $mpdLng = $degree*cos($latitude * ($PI/180));
    $dpmLng = 1 / $mpdLng;
    $radiusLng = $dpmLng*$raidus;
    $minLng = $longitude - $radiusLng;
    $maxLng = $longitude + $radiusLng;
    return array (minLat=>$minLat, maxLat=>$maxLat, minLng=>$minLng, maxLng=>$maxLng);
}

function getPoiByLon($latitude,$longitude,$raidus)
{
    $distanceSql = ",".distance_sql($longitude,$latitude,"longitude","latitude")." as distance";
    $orderBy = " distance";
    $kmStr = "< ".$raidus;

    $location = getAround($latitude,$longitude,$raidus);

    $wheres1 = "(1=1) ";
    $wheres2 = " where (1=1)";
    //$wheres1.=" and (((latitude >= {$location["minLat"]}) and (latitude <= {$location['maxLat']})) and ((longitude >= {$location['minLng']}) and (longitude <= {$location['maxLng']})))";

    //$wheres2 .= " and (location.distance <= {$raidus})";

    $field = "*{$distanceSql}";
    $sql = "select location.* from (select {$field} from pois where {$wheres1} order by {$orderBy} limit 0,10) location {$wheres2}";
    return $sql;
}

 echo getPoiByLon(33.004283,118.548442,10000);exit;